#Criação do banco de dados:
CREATE DATABASE ProjetoPosto;
USE ProjetoPosto;

#Criação das tabelas:
CREATE TABLE Paciente (
    id_paciente INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cpf CHAR(11) NOT NULL UNIQUE CHECK (CHAR_LENGTH(cpf) = 11),
    data_nascimento DATE NOT NULL CHECK (data_nascimento >= '1900-01-01'),
    telefone VARCHAR(15) CHECK (telefone IS NULL OR CHAR_LENGTH(telefone) BETWEEN 10 AND 15),
    endereco VARCHAR(150)
);

CREATE TABLE Especialidade (
    id_especialidade INT AUTO_INCREMENT PRIMARY KEY,
    descricao VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Profissional (
    id_profissional INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    crm VARCHAR(20) NOT NULL UNIQUE CHECK (CHAR_LENGTH(crm) >= 4),
    telefone VARCHAR(15) CHECK (telefone IS NULL OR CHAR_LENGTH(telefone) BETWEEN 10 AND 15),
    id_especialidade INT NOT NULL,

    FOREIGN KEY (id_especialidade) REFERENCES Especialidade(id_especialidade)
);

CREATE TABLE Agendamento (
    id_agendamento INT AUTO_INCREMENT PRIMARY KEY,
    data DATE NOT NULL,
    hora TIME NOT NULL,
    status ENUM('AGENDADO', 'CONFIRMADO', 'CANCELADO', 'REALIZADO') NOT NULL,
    id_paciente INT NOT NULL,

    FOREIGN KEY (id_paciente) REFERENCES Paciente(id_paciente)
);

CREATE TABLE Consulta (
    id_consulta INT AUTO_INCREMENT PRIMARY KEY,
    data DATE NOT NULL,
    hora TIME NOT NULL,
    motivo TEXT NOT NULL,
    diagnostico TEXT,
    observacoes TEXT,
    id_profissional INT NOT NULL,
    id_agendamento INT NOT NULL UNIQUE,

    FOREIGN KEY (id_profissional) REFERENCES Profissional(id_profissional),
    FOREIGN KEY (id_agendamento) REFERENCES Agendamento(id_agendamento)
);

CREATE TABLE Receita (
    id_receita INT AUTO_INCREMENT PRIMARY KEY,
    data DATE NOT NULL,
    instrucoes TEXT,
    id_consulta INT NOT NULL UNIQUE,
    FOREIGN KEY (id_consulta) REFERENCES Consulta(id_consulta)
);

CREATE TABLE Medicamento (
    id_medicamento INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao TEXT
);

CREATE TABLE Item_Receita (
    id_item_receita INT AUTO_INCREMENT PRIMARY KEY,
    dose VARCHAR(50) NOT NULL,
    quantidade INT NOT NULL CHECK (quantidade > 0),
    id_receita INT NOT NULL,
    id_medicamento INT NOT NULL,

    FOREIGN KEY (id_receita) REFERENCES Receita(id_receita),
    FOREIGN KEY (id_medicamento) REFERENCES Medicamento(id_medicamento)
);


CREATE TABLE Lote_Medicamento (
    id_lote_medicamento INT AUTO_INCREMENT PRIMARY KEY,
    numero_lote VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Item_Estoque (
    id_item_estoque INT AUTO_INCREMENT PRIMARY KEY,
    quantidade INT NOT NULL CHECK (quantidade >= 0),
    validade DATE NOT NULL CHECK (validade > '2000-01-01'),
    id_medicamento INT NOT NULL,
    id_lote_medicamento INT NOT NULL,

    FOREIGN KEY (id_medicamento) REFERENCES Medicamento(id_medicamento),
    FOREIGN KEY (id_lote_medicamento) REFERENCES Lote_Medicamento(id_lote_medicamento)
);

CREATE TABLE Fabricante (
    id_fabricante INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Vacina (
    id_vacina INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    id_fabricante INT NOT NULL,

    FOREIGN KEY (id_fabricante) REFERENCES Fabricante(id_fabricante)
);

CREATE TABLE Lote_Vacina (
    id_lote_vacina INT AUTO_INCREMENT PRIMARY KEY,
    numero_lote VARCHAR(50) NOT NULL UNIQUE,
    validade DATE NOT NULL CHECK (validade > '2000-01-01'),
    id_vacina INT NOT NULL,

    FOREIGN KEY (id_vacina) REFERENCES Vacina(id_vacina)
);

CREATE TABLE AplicacaoVacina (
    id_aplicacao INT AUTO_INCREMENT PRIMARY KEY,
    data_aplicacao DATE NOT NULL,
    dose INT NOT NULL CHECK (dose > 0),
    observacoes TEXT,
    id_paciente INT NOT NULL,
    id_lote_vacina INT NOT NULL,

    FOREIGN KEY (id_paciente) REFERENCES Paciente(id_paciente),
    FOREIGN KEY (id_lote_vacina) REFERENCES Lote_Vacina(id_lote_vacina)
);

#Criação e definição de permissões para os usuários:
CREATE USER 'administrador'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON ProjetoPosto.* TO 'administrador'@'localhost';

CREATE USER 'funcionario'@'localhost' IDENTIFIED BY 'funcionario';
GRANT SELECT, INSERT ON ProjetoPosto.* TO 'funcionario'@'localhost';

FLUSH PRIVILEGES;

#Inserção de registros nas tabelas (na sequência de criação):
INSERT INTO Especialidade (descricao) VALUES
('Clínica Geral'), ('Pediatria'), ('Ginecologia'), ('Cardiologia'), ('Dermatologia'), ('Ortopedia');

INSERT INTO Profissional (nome, crm, telefone, id_especialidade) VALUES
('Dr. Carlos Mendes', 'CRM12345', '47999990001', 1),
('Dra. Fernanda Souza', 'CRM23456', '47999990002', 2),
('Dra. Juliana Alves', 'CRM34567', '47999990003', 3),
('Dr. Ricardo Lima', 'CRM45678', '47999990004', 4),
('Dra. Patricia Rocha', 'CRM56789', '47999990005', 5),
('Dr. Eduardo Santos', 'CRM67890', '47999990006', 6);

INSERT INTO Paciente (nome, cpf, data_nascimento, telefone, endereco) VALUES
('Ana Beatriz Costa', '12345678901', '1990-05-14', '47988880001', 'Rua das Flores, 123'),
('Bruno Henrique Silva', '23456789012', '1985-11-02', '47988880002', 'Av. Brasil, 456'),
('Camila Ferreira', '34567890123', '2000-02-20', '47988880003', 'Rua XV de Novembro, 789'),
('Diego Martins', '45678901234', '1978-07-30', '47988880004', 'Rua Sete de Setembro, 321'),
('Elaine Cristina Oliveira', '56789012345', '1995-09-09', '47988880005', 'Rua Rio Branco, 654'),
('Fabio Rodrigues', '67890123456', '1960-12-25', '47988880006', 'Rua Getúlio Vargas, 987');

INSERT INTO Agendamento (data, hora, status, id_paciente) VALUES
('2026-07-10', '08:00:00', 'REALIZADO', 1),
('2026-07-10', '09:00:00', 'REALIZADO', 2),
('2026-07-11', '10:30:00', 'REALIZADO', 3),
('2026-07-11', '14:00:00', 'REALIZADO', 4),
('2026-07-12', '08:30:00', 'REALIZADO', 5),
('2026-07-12', '15:00:00', 'REALIZADO', 6),
('2026-07-15', '09:00:00', 'AGENDADO', 1),
('2026-07-16', '11:00:00', 'CANCELADO', 3);

INSERT INTO Consulta (data, hora, motivo, diagnostico, observacoes, id_profissional, id_agendamento) VALUES
('2026-07-10', '08:00:00', 'Dor de cabeça recorrente', 'Enxaqueca', 'Paciente orientado a retornar em 30 dias', 1, 1),
('2026-07-10', '09:00:00', 'Consulta de rotina pediátrica', 'Desenvolvimento normal', NULL, 2, 2),
('2026-07-11', '10:30:00', 'Check-up ginecológico anual', 'Sem alterações', NULL, 3, 3),
('2026-07-11', '14:00:00', 'Dor no peito', 'Suspeita de arritmia', 'Encaminhado para exames complementares', 4, 4),
('2026-07-12', '08:30:00', 'Lesão de pele suspeita', 'Dermatite de contato', NULL, 5, 5),
('2026-07-12', '15:00:00', 'Dor no joelho após queda', 'Entorse leve', 'Repouso recomendado por 7 dias', 6, 6);

INSERT INTO Receita (data, instrucoes, id_consulta) VALUES
('2026-07-10', 'Tomar 1 comprimido a cada 8 horas por 5 dias', 1),
('2026-07-10', 'Tomar 1 comprimido a cada 6 horas se necessário', 2),
('2026-07-11', 'Uso conforme orientação médica', 3),
('2026-07-11', 'Uso contínuo conforme prescrição cardiológica', 4),
('2026-07-12', 'Aplicar pomada 2x ao dia por 10 dias', 5),
('2026-07-12', 'Repouso e uso de anti-inflamatório por 7 dias', 6);

INSERT INTO Medicamento (nome, descricao) VALUES
('Dipirona Sódica', 'Analgésico e antitérmico'),
('Paracetamol', 'Analgésico e antitérmico'),
('Amoxicilina', 'Antibiótico de amplo espectro'),
('Losartana', 'Anti-hipertensivo'),
('Betametasona Creme', 'Corticoide tópico'),
('Ibuprofeno', 'Anti-inflamatório não esteroidal');

INSERT INTO Item_Receita (dose, quantidade, id_receita, id_medicamento) VALUES
('500mg', 20, 1, 1),
('750mg', 12, 2, 2),
('500mg', 21, 3, 3),
('50mg', 30, 4, 4),
('aplicação local', 1, 5, 5),
('400mg', 10, 6, 6);

INSERT INTO Lote_Medicamento (numero_lote) VALUES
('LOTE-001'),
('LOTE-002'),
('LOTE-003'),
('LOTE-004'),
('LOTE-005'),
('LOTE-006');